<?php
/*
 * Copyright 2008 by Oliver Radwan, Maxwell Palmer, Nolan McNair,
 * Taylor Talmage, and Allen Tucker.  This program is part of RMH Homebase.
 * RMH Homebase is free software.  It comes with absolutely no warranty.
 * You can redistribute it and/or modify it under the terms of the GNU
 * General Public License as published by the Free Software Foundation
 * (see <http://www.gnu.org/licenses/ for more information).
*/
include_once('dbinfo.php');
/**
 * Functions to create, update, and retrieve information from the
 * dbSchedules table in the database.  This table contains the master
 * schedules for volunteers that are maintained by the House Manager.
 * These schedules provide the basis for generating each new month's
 * calendar, which is a series of shifts in the dbShifts table.
 * @version May 1, 2008
 * @author Allen Tucker and Maxwell Palmer
 */

/* Create an empty table of shifts for odd and even weeks and weekends
 * into which a volunteer can be scheduled.
 * group = "One", "Two", or "Wkd"
 * day = "Mon", "Tue", ... "Sun"
 * time = "9-12", "12-3", "3-6", "6-9", "10-1", "1-4", "2-5, or "5-9"
 * slots = the number of slots to be filled for this shift
 * persons is a comma-separated list of ids, like "allen7298111"
 * notes = any notes that will be displayed for this shift on the schedule
 *
 */
	function setup_dbSchedules () {
		connect();
		mysql_query("DROP TABLE IF EXISTS dbSchedules");
		$result = mysql_query("CREATE TABLE dbSchedules (my_group TEXT, day TEXT, time TEXT, slots INT, persons TEXT, notes TEXT)");
        if (!$result)
		    die ("Could not create dbSchedules table: " . mysql_error());
	    $weekdays = array('Mon','Tue','Wed','Thu','Fri');
		$times = array('9-12','12-3','3-6','6-9');
		$prefix = "INSERT INTO dbSchedules VALUES (";
		foreach ($weekdays as $weekday) {
		  foreach ($times as $time) {
		  	if ($time != "6-9") {   // don't generate 6-9 schedule for Family Room
		  	    mysql_query($prefix . "'One', '" . $weekday . "','" . $time . "','" . 2 . "', '', '')");
		  	    mysql_query($prefix . "'Two', '" . $weekday . "','" . $time . "','" . 2 . "', '', '')");
		  	    mysql_query($prefix . "'FamOne', '" . $weekday . "','" . $time . "','" . 1 . "', '', '')");
		  	   	mysql_query($prefix . "'FamTwo', '" . $weekday . "','" . $time . "','" . 1 . "', '', '')");
		  	}
		  	else if ($weekday!="Fri"){
		  		mysql_query($prefix . "'One', '" . $weekday . "','" . $time . "','" . 3 . "', '', '')");
		  	    mysql_query($prefix . "'Two', '" . $weekday . "','" . $time . "','" . 3 . "', '', '')");
		  	}
		  	else {
		  		mysql_query($prefix . "'One', '" . $weekday . "','" . "night" . "','" . 1 . "', '', '')");
		  	    mysql_query($prefix . "'Two', '" . $weekday . "','" . "night" . "','" . 1 . "', '', '')");
		  	}
		  }
		}
        for ($i=1; $i<=5; $i++) {
			$ithsat = "Sat" . $i;
		    mysql_query($prefix . "'Wkd', '" . $ithsat . "', '10-1', 1," . "'', '')");
		    mysql_query($prefix . "'Wkd', '" . $ithsat . "', '1-4', 1," . "'', '')");
		    mysql_query($prefix . "'Wkd', '" . $ithsat . "', 'night', 1," . "'', '')");
		}
        for ($i=1; $i<=5; $i++) {
			$ithsun = "Sun" . $i;
		    mysql_query($prefix . "'Wkd', '" . $ithsun . "', '9-12', 1," . "'', '')");
		    mysql_query($prefix . "'Wkd', '" . $ithsun . "', '2-5', 1," . "'', '')");
		    mysql_query($prefix . "'Wkd', '" . $ithsun . "', '5-9', 1," . "'', '')");
		}
		mysql_close();
	}

   /* schedule a person for a given day and time in group One, Two, or Wkd
    * update that person's schedule in the dbPersons table
    *
    */
	function schedule_person($group, $day, $time, $person_id){
		connect();
		$query1 = "SELECT * FROM dbSchedules WHERE my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$query2 = "SELECT * FROM dbPersons WHERE id = '" . $person_id . "'";
		$result = mysql_query($query1);
	    $resultp = mysql_query($query2);
	    if (!$result || !$resultp) die ("could not query the database");
	    // be sure the person exists and is scheduled
	    if (mysql_num_rows($result) !== 1 || mysql_num_rows($resultp) !== 1) {
	       mysql_close();
	       return "Error: group-day-time not valid or person not in database";
	    }
	    $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	    $resultp_row = mysql_fetch_array($resultp, MYSQL_ASSOC);
        $persons = explode(',', $result_row['persons']);    // get an array of scheduled person id's
        $schedule = explode(',', $resultp_row['schedule']); // get an array of person's scheduled times
        $availability = explode(',', $resultp_row['availability']);     // and their availabiltiy
        if (in_array(substr($day,0,3).$time, $availability) &&
            !in_array($person_id, $persons) &&
            !in_array($group.substr($day,0,3).$time, $schedule)) {
           $persons[] = $person_id;             // add the person to the array, and
           $schedule[] = $group . $day . $time; // add the time to the person's schedule
           $result_row['persons'] = implode(',', $persons);     // and update one row in each table
           $resultp_row['schedule'] = implode(',', $schedule);  // in the database
           mysql_query("UPDATE dbSchedules SET persons = '". $result_row['persons'] . "' WHERE my_group = '" .
                        $group . "' AND day = '" . $day . "' AND time = '" . $time . "'");
	       mysql_query("UPDATE dbPersons SET schedule = '" . $resultp_row['schedule'] .
	       		               "' WHERE id = '" . $person_id . "'");
           mysql_close();
           return "";
        }
        mysql_close();
        return "Error: can't schedule a person not available or already scheduled";
	}
    /* unschedule a volunteer from group One, Two, or Wkd at a given day and time
     * update that person's schedule in the dbPersons table
     *
     */
    function unschedule_person ($group, $day, $time, $person_id) {
    	connect();
		$query = "SELECT * FROM dbSchedules WHERE my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$queryp = "SELECT * FROM dbPersons WHERE id = '" . $person_id . "'";
		$result = mysql_query($query);
	    $resultp = mysql_query($queryp);
	    // be sure the person exists and is scheduled
	    if (!$result || mysql_num_rows($result) !== 1) {
	       mysql_close();
	       return "Error: group-day-time not valid";
	    }
	    else if (!$resultp || mysql_num_rows($resultp) !== 1) {
	       $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	       $persons = explode(',', $result_row['persons']);    // get an array of scheduled person id's
           if (in_array($person_id, $persons)) {
				$index = array_search($person_id, $persons);
				array_splice($persons, $index, 1);               // remove the person from the array, and
				$result_row['persons'] = implode(',', $persons); // and update one row in the schedule
                mysql_query("UPDATE dbSchedules SET persons = '". $result_row['persons'] . "' WHERE my_group = '" .
                        $group . "' AND day = '" . $day . "' AND time = '" . $time . "'");
           }
           mysql_close();
	       return "Error: person not in database";
	    }
	    $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	    $resultp_row = mysql_fetch_array($resultp, MYSQL_ASSOC);
        $persons = explode(',', $result_row['persons']);    // get an array of scheduled person id's
        $schedule = explode(',', $resultp_row['schedule']); // get an array of person's scheduled times
        if (in_array($person_id, $persons) && in_array($group.$day.$time, $schedule)) {
           $index = array_search($person_id, $persons);
           $indexp = array_search($group.$day.$time, $schedule);
           array_splice($persons, $index, 1);   // remove the person from the array, and
           array_splice($schedule, $indexp, 1); // remove the time from the person's schedule
           $result_row['persons'] = implode(',', $persons);     // and update one row in each table
           $resultp_row['schedule'] = implode(',', $schedule);  // in the database
           mysql_query("UPDATE dbSchedules SET persons = '". $result_row['persons'] . "' WHERE my_group = '" .
                        $group . "' AND day = '" . $day . "' AND time = '" . $time . "'");
	       mysql_query("UPDATE dbPersons SET schedule = '" . $resultp_row['schedule'] .
	       		               "' WHERE id = '" . $person_id . "'");
	       mysql_close();
           return "";
        }
        mysql_close();
 		return "Error: can't unschedule a person not scheduled";
    }
    /* insert a note in the scedule for a given group, day, and time.
     *
     */
    function make_notes ($group, $day, $time, $notes) {
    	connect();
		$query = "SELECT * FROM dbSchedules WHERE my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$result = mysql_query($query);
	    if (!$result) die ("could not query the database");
	    // be sure the person exists and is scheduled
	    if (mysql_num_rows($result) !== 1) {
	       mysql_close();
	       return "Error: group-day-time not valid";
	    }
	    $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	    $result_row['notes'] = $notes;
	    mysql_query("UPDATE dbSchedules SET notes = '". $result_row['notes'] . "' WHERE my_group = '" .
                        $group . "' AND day = '" . $day . "' AND time = '" . $time . "'");
	    mysql_close();
        return "";
    }
    /*
	 * @return whether or not a person is scheduled in a given group, day and time
	 *
	 */
	function is_scheduled ($group, $day, $time, $person_id) {
		connect();
		$query = "SELECT * FROM dbSchedules WHERE my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$result = mysql_query($query);
	    if (!$result) die ("could not query the database");
	    if (mysql_num_rows($result) !== 1) {
	       mysql_close();
	       return "Error: group-day-time not valid";
	    }
	    $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
		$persons = explode(',', $result_row['persons']);    // get array of scheduled person id's
        mysql_close();
        if (in_array($person_id, $persons))
           return true;
        else return false;
	}
	/*
	 * @return all persons scheduled for a particular group, day, and time
	 * as an array of associative arrays.  Each associative array has
	 * entries indexed by the field names of a person in dbPersons.
	 */
	function get_persons($group, $day, $time) {
        connect();
		$query1 = "SELECT * FROM dbSchedules WHERE my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$result = mysql_query($query1);
	    if (!$result) die ("could not query the database");
	    $out = array();
	    if (mysql_num_rows($result) !== 1) {
	       mysql_close();
	       $out[] = "Error: group-day-time not valid";
	       return $out;
	    }
	    $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	    $person_ids = explode(',', $result_row['persons']);    // get an array of scheduled person id's
	    foreach ($person_ids as $person_id)
	      if ($person_id!="") {
            $query2 = "SELECT * FROM dbPersons WHERE id = '" . $person_id . "'";
		    $resultp = mysql_query($query2);
            if (!$resultp) die ("could not query the database");
	        if (mysql_num_rows($resultp) !== 1) {
	           mysql_close();
	           $out[] = $person_id;
	           return $out;
	        }
	        $out[] = mysql_fetch_array($resultp, MYSQL_ASSOC);
	      }
        mysql_close();
        return $out;
	}
    /*
	 * @return ids of all persons scheduled for a particular group, day, and time
     */
	function get_person_ids($group, $day, $time) {
        connect();
		$query1 = "SELECT * FROM dbSchedules WHERE my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$result = mysql_query($query1);
	    if (!$result) die ("could not query the database");
	    if (mysql_num_rows($result) !== 1) {
	       mysql_close();
	       return array("Error: group-day-time not valid");
	    }
	    $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	    $person_ids = explode(',', $result_row['persons']);
	    mysql_close();
	    return $person_ids;
	}
    /*
	 * @return number of slots for a particular group, day, and time
     */
	function get_total_slots($group, $day, $time) {
        connect();
		$query1 = "SELECT * FROM dbSchedules WHERE my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$result = mysql_query($query1);
	    if (!$result) die ("could not query the database");
	    if (mysql_num_rows($result) !== 1) {
	       mysql_close();
	       return "Error: group-day-time not valid";
	    }
	    $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	    mysql_close();
	    return $result_row['slots'];
	}
    /*
	 * @return number of vacancies for a particular group, day, and time
     */
	function get_total_vacancies($group,$day,$time) {
		$slots=get_total_slots($group,$day,$time);
		$persons=count(get_persons($group,$day,$time));
		return $slots-$persons;
	}
    /*
	 * @return number of vacancies for a particular group, day, and time
     */
	function check_valid_schedule($group, $day, $time) {
      connect();
		$query1 = "SELECT * FROM dbSchedules WHERE my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$result = mysql_query($query1);
		mysql_close();
	    if (!$result) die ("could not query the database");
	    if (mysql_num_rows($result) !== 1) {
	       return false;
	    }
	    return true;
	}
	/*
	 * @return number of vacancies for a particular group, day, and time
     */
	function edit_schedule_vacancy($group,$day,$time,$change) {
		connect();
		$query1 = "SELECT * FROM dbSchedules WHERE my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$result = mysql_query($query1);
	    if (!$result) die ("could not query the database");
	    if (mysql_num_rows($result) !== 1) {
	       mysql_close();
	       return false;
	    }
		 $result_row=mysql_fetch_array($result,MYSQL_ASSOC);
		 $result_row['slots']=$result_row['slots']+$change;
		 mysql_query("UPDATE dbSchedules SET slots = '". $result_row['slots'] . "' WHERE my_group = '" .
                 $group . "' AND day = '" . $day . "' AND time = '" . $time . "'");
		 mysql_close();
	    return true;
	}
    /*
     * @return master schedule as a big associative array
     */
    function get_dbSchedules() {
		connect();
		$query="SELECT * FROM dbSchedules";
		$result = mysql_query($query);
		if (!$result)
		    die ("Could not query the database: <br />" . mysql_error());
		$outcome = array();
		for($i=0;$i<mysql_num_rows($result);$i++) {
			$outcome[] = mysql_fetch_array($result, MYSQL_ASSOC);
		}
		mysql_close();
		return $outcome;
	}

?>
